/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package dao;

import db.DBAccess;
import entity.Manager;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author DucND
 */
public class ManagerDAO {
    
    public static List<Manager> getManagerList() {
        List<Manager> managerList = new ArrayList<Manager>();
        try {
            Connection conn = DBAccess.getConnection();
            ResultSet rs = null;
            PreparedStatement prst = conn.prepareStatement("Select * from Manager");
            rs = prst.executeQuery();
            while (rs != null && rs.next()) {
                Manager ma = new Manager();
                ma.setUsername(rs.getString("Username"));
                ma.setPassword(rs.getString("Password"));
                ma.setEmail(rs.getString("Email"));
                ma.setRole(rs.getBoolean("Role"));
                managerList.add(ma);
            }
        } catch (SQLException ex) {
            Logger.getLogger(StudentDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return managerList;
    }
    
    public static void add(Manager m) { 
       try {
            Connection conn = DBAccess.getConnection();
            Statement st = conn.createStatement();
            String query = "Insert into Manager values('" + m.getUsername() 
                    + "', '" + m.getPassword() + "', '" + m.getEmail() + "', '" + m.getRole() + "')";
            st.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(ManagerDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public static void delete(String username){
         try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con =
                    DriverManager.getConnection("jdbc:odbc:SMS");
            PreparedStatement prst =
                    con.prepareStatement("Delete from Manager where UserName = '"+username+"'");
            prst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(ManagerDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ManagerDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public static void edit(Manager sta){
        try {
            Connection con = DBAccess.getConnection();
            PreparedStatement prst =
                    con.prepareStatement("Update Manager set Password = '" + sta.getPassword() + "', Email = '" + sta.getEmail() + "', Role = "+(sta.getRole() == true ? 1 : 0)+" where Username = '"+sta.getUsername()+"'");
            prst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(ManagerDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
    
    public static Manager getManagerInfo(String Username){
        Manager sta= new Manager();
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con = DriverManager.getConnection("jdbc:odbc:SMS");
            PreparedStatement prst =
                    con.prepareStatement("select * from Manager where Username = '"+Username+"'");
            ResultSet rs = prst.executeQuery();
            rs.next();
            sta.setUsername(rs.getString("Username"));
            sta.setPassword(rs.getString("Password"));
            sta.setEmail(rs.getString("Email"));
            sta.setRole(rs.getBoolean("Role"));
        } catch (SQLException ex) {
            Logger.getLogger(ManagerDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ManagerDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return sta;
    }
    
      public static Manager getManagerInfoByEmail(String email){
        Manager sta= new Manager();
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con = DriverManager.getConnection("jdbc:odbc:SMS");
            PreparedStatement prst =
                    con.prepareStatement("select * from Manager where Email = '"+email+"'");
            ResultSet rs = prst.executeQuery();
            rs.next();
            sta.setUsername(rs.getString("Username"));
            sta.setPassword(rs.getString("Password"));
            sta.setEmail(rs.getString("Email"));
            sta.setRole(rs.getBoolean("Role"));
        } catch (SQLException ex) {
            Logger.getLogger(ManagerDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ManagerDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return sta;
    }
    
    public static String login(String username, String password) {
        Connection conn = DBAccess.getConnection();
        try {
            PreparedStatement prst = conn.prepareStatement("Select Username from Manager where Username = ? and Password = ?");
            prst.setString(1, username);
            prst.setString(2, password);
            ResultSet rs = prst.executeQuery();
            if (rs != null && rs.next()) {
                return rs.getString("Username");
            }
        } catch (SQLException ex) {
            Logger.getLogger(ManagerDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(ManagerDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return null;
    }
    
    public static boolean isAdmin(String username) {
        try {
            Connection conn = DBAccess.getConnection();
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("Select Role as Role from Manager where Username = '" + username + "'");
            if (rs != null && rs.next()) {
                return rs.getBoolean("Role");
            }
        } catch (SQLException ex) {
            Logger.getLogger(ManagerDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    
    public static boolean isExist(String type, String value) {
        Connection conn = DBAccess.getConnection();
        try {
            PreparedStatement prst = conn.prepareStatement("Select * from Manager where " + type + " = ?");
            prst.setString(1, value);
            ResultSet rs = prst.executeQuery();
            if (rs != null && rs.next()) {
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(ManagerDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(ManagerDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return false;
    }
}
